﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data;
using DMS.BaseClass;
using DMS.Business.Modules.SupplierManagement;
using Microsoft.Practices.EnterpriseLibrary.Data;
using DMS.Base;
using DMS.Business.BaseClass;

namespace DMS.Business.Utility
{
    public class ContractHelper : BaseService
    {
        #region SQL查询句子
        private const string SQL_SPM_ContractInfo_Count = @"SELECT COUNT(*) AS Total
                                                            FROM [dbo].[SPM_Contracts] as t1
                                                            @joinClause@
                                                        ";

        private const string SQL_SPM_ContractInfo = @"select {0} t1.*,t2.Name as SupplierName,t3.ProjectName from SPM_Contracts t1 
                                                            inner join SPM_Suppliers t2 on t1.SupplierID = t2.SupplierID
                                                            inner join SPM_Projects t3 on t1.ProjectID = t3.ProjectID
                                                            @joinClause@
                                                        ";

        private const string SQL_SPM_ContractInfo_Order = @"ORDER BY [CreateTime] DESC";

        private const string SQL_SPM_ContractInfo_Add = @"INSERT INTO [dbo].[SPM_Contracts]
                                                               ([ContractID]
                                                               ,[SupplierID]
                                                               ,[ProjectID]
                                                               ,[ContractNo]
                                                               ,[ContractName]
                                                               ,[ContractMoney]
                                                               ,[ClosedMoney]
                                                               ,[StartDate]
                                                               ,[EndDate]
                                                               ,[Status]
                                                               ,[Creator]
                                                               ,[CreatorID]
                                                               ,[CreateTime]
                                                               ,[OwnerDeptID])
                                                         select @ContractID
                                                               ,@SupplierID
                                                               ,@ProjectID
                                                               ,@ContractNo
                                                               ,@ContractName
                                                               ,@ContractMoney
                                                               ,@ClosedMoney
                                                               ,@StartDate
                                                               ,@EndDate
                                                               ,@Status
                                                               ,@Creator
                                                               ,@CreatorID
                                                               ,@CreateTime
                                                               ,dbo.f_GetTypeDeptID(ou.DeptID)
                                                             from org_users ou where ou.userid=@userID";

        private const string SQL_SPM_ContractInfo_Edit = @"UPDATE [dbo].[SPM_Contracts]
                                                           SET [ContractID] = @ContractID
                                                              ,[SupplierID] = @SupplierID
                                                              ,[ProjectID] = @ProjectID
                                                              ,[ContractNo] = @ContractNo
                                                              ,[ContractName] = @ContractName
                                                              ,[ContractMoney] = @ContractMoney
                                                              ,[ClosedMoney] = @ClosedMoney
                                                              ,[StartDate] = @StartDate
                                                              ,[EndDate] = @EndDate
                                                              ,[Status] = @Status
                                                              ,[Creator] = @Creator
                                                              ,[CreatorID] = @CreatorID
                                                              ,[CreateTime] = @CreateTime
                                                         WHERE [ContractID] = @ContractID";

        private const string SQL_SPM_ContractInfo_Del = @"DELETE FROM [dbo].[SPM_Contracts]
                                                          WHERE [ContractID] = @ContractID";
        #endregion

        public static ContractInfo GetContractByID(string id)
        {
            int total = 0;
            string condition = string.Format("AND [ContractID] = '{0}'", id.ToString().ToUpper());
            List<ContractInfo> list = GetContractInfos(1, 0, out total, condition, true);

            ContractInfo info = null;
            if (list.Count > 0)
            {
                info = list[0];
            }

            return info;
        }

        public static List<ContractInfo> GetContractsByProjectID(string projectID)
        {
            //List<ContractInfo> all = GetAllContracts();
            //for (int i = 0; i < all.Count; i++)
            //{
            //    if (all[i].ProjectID != projectID)
            //    {
            //        all.RemoveAt(i);
            //        i--;
            //    }
            //}
            //return all;

            int total = 0;
            string condition = string.Format(" and t1.ProjectID='{0}'",projectID.ToString().ToUpper());
            List<ContractInfo> list = GetContractInfos(0, 0, out total, condition, true);
            return list;
        }

        /// <summary>
        /// 获取当前有效的合同
        /// </summary>
        /// <param name="projectName">项目名称</param>
        /// <returns></returns>
        public static List<ContractInfo> GetCurrentContracts(string projectName)
        {
            string sql = @"
select t1.*,t2.Name as SupplierName,t3.ProjectName from SPM_Contracts t1 
inner join SPM_Suppliers t2 on t1.SupplierID = t2.SupplierID
inner join SPM_Projects t3 on t1.ProjectID = t3.ProjectID
where t3.ProjectName='{0}'and getdate() between t1.StartDate and t1.EndDate
";
            sql = string.Format(sql, projectName);


            DbCommand command = DBHelper.Instance.GetSqlStringCommand(sql);

            DataSet ds = DBHelper.Instance.ExecuteDataSet(command);
            List<ContractInfo> list = new List<ContractInfo>();
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    ContractInfo info = new ContractInfo(dr["ContractID"].ToString());
                    FillContractInfoContent(dr, info);

                    list.Add(info);
                }
            }
            return list;
        }

        public static List<ContractInfo> GetAllContracts()
        {
            int total = 0;
            return GetContractInfos(0, 0, out total, "",false);
        }

        public static List<ContractInfo> GetAllContractsIncludeOutskirts()
        {
            int total = 0;
            return GetContractInfos(0, 0, out total, "",true);
        }

        /// <summary>
        /// 获取合同信息列表
        /// </summary>
        /// <param name="limite">返回数量限制，-1为不限制</param>
        /// <param name="page">页码</param>
        /// <param name="total">总记录数</param>
        /// <param name="condition">查询条件</param>
         /// <param name="includeOutskirts">是否包含郊市</param>
        /// <returns></returns>
        public static List<ContractInfo> GetContractInfos(int limite, int page, out int total, string condition, bool includeOutskirts)
        {
            total = 0;
            Database db = DBHelper.Instance;
            string topClause = string.Empty;

            string joinClause = string.Empty;
            string andClause = string.Empty;
            string userid = SystemContext.Current.LoginUser == null ? "" : SystemContext.Current.LoginUser.ID.ToString();
            if (!string.IsNullOrEmpty(userid) && !includeOutskirts)
            {
                joinClause = "join org_users ou on t1.OwnerDeptID = dbo.f_GetTypeDeptID(ou.deptid)";
                andClause = " and ou.userid='" + userid + "' ";
            }

            string whereClause = string.Format(" where 1 = 1 {0}", condition);

            string countSql = string.Format("{0} {1}  @andClause@", SQL_SPM_ContractInfo_Count, whereClause);

            countSql = countSql.Replace("@joinClause@", joinClause).Replace("@andClause@", andClause);

            if (limite > 0)
            {
                topClause = string.Format("Top {0}", limite.ToString());

                if (page > 1)
                {
                    int pageCount = (page - 1) * limite;
                    string subClause =
                        string.Format(
                            @"SELECT TOP {0} [ContractID] FROM [dbo].[SPM_Contracts] @joinClause@ {1} @andClause@ {2}",
                            pageCount.ToString(), whereClause, SQL_SPM_ContractInfo_Order);
                    subClause = subClause.Replace("@joinClause@", joinClause).Replace("@andClause@", andClause);
                    whereClause += string.Format("AND [ContractID] NOT IN({0})", subClause);
                }
            }
            string mainClause = string.Format(SQL_SPM_ContractInfo, topClause);
            string sql = string.Format("{0} {1} @andClause@ {2}", mainClause, whereClause, SQL_SPM_ContractInfo_Order);

            sql = sql.Replace("@joinClause@", joinClause).Replace("@andClause@", andClause);

            DbCommand command = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(command);
            List<ContractInfo> list = new List<ContractInfo>();
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                total = GetRecordCount(countSql, null);
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    ContractInfo info = new ContractInfo(dr["ContractID"].ToString());
                    FillContractInfoContent(dr, info);

                    list.Add(info);
                }
            }

            return list;
        }

        public static void AddContractInfo(ContractInfo info)
        {
            ContractInfo info2 = GetContractByID(info.ContractID);
            if (info2 != null)
            {
                UpdateContractInfo(info);
                return;
            }

            Database db = DBHelper.Instance;
            DbCommand cmd = db.GetSqlStringCommand(SQL_SPM_ContractInfo_Add);
            FillContractInfoParams(cmd, info);

            db.ExecuteNonQuery(cmd);
        }
        public static void UpdateContractInfo(ContractInfo info)
        {
            Database db = DBHelper.Instance;
            DbCommand cmd = db.GetSqlStringCommand(SQL_SPM_ContractInfo_Edit);
            FillContractInfoParams(cmd, info);

            db.ExecuteNonQuery(cmd);
        }
        public static void DeleteMaterialInfo(ContractInfo mInfo)
        {
            Database db = DBHelper.Instance;
            DbCommand cmd = db.GetSqlStringCommand(SQL_SPM_ContractInfo_Del);
            FillContractInfoParams(cmd, mInfo);

            db.ExecuteNonQuery(cmd);
        }
        /// <summary>
        /// 更新已结算金额
        /// </summary>
        /// <param name="info"></param>
        public static void UpdateClosedMoney(string contractID,double money)
        {
            ContractInfo info = GetContractByID(contractID);
            if (info != null)
            {
                info.ClosedMoney = money;
                info.Save();
            }
            else
            {
                throw new Exception("合同不存在，ID为："+contractID.ToString());
            }
        }

        /// <summary>
        /// 获取再过四个月就到期的合同
        /// </summary>
        public static DataSet GetExpireContracts()
        {
            string sql = @"
select distinct CT.ProjectID,CT.ContractName,CT.EndDate,MC.ClassAdmin,MC.ClassAdminID
from dbo.SPM_Contracts CT
inner join dbo.SPM_Projects PJ on CT.ProjectID=PJ.ProjectID
inner join dbo.MIM_MaterialClass MC on PJ.ClassID=MC.ClassID
where Dateadd(month,4,getdate())>CT.EndDate and CT.IsRemind=0
";
            return DBHelper.Instance.ExecuteDataSet(CommandType.Text, sql);
        }

        /// <summary>
        /// 更新合同提醒状态为已提醒
        /// </summary>
        /// <param name="projectID"></param>
        /// <param name="contractName"></param>
        /// <param name="endDate"></param>
        public static void UpdateRemindStatus(string projectID,string contractName,DateTime endDate)
        {
            string sql = string.Format("update SPM_Contracts set IsRemind=1 where ProjectID='{0}' and ContractName='{1}' and EndDate='{2}'",projectID,contractName,endDate);
            DBHelper.Instance.ExecuteNonQuery(CommandType.Text, sql);
        }

        

        /// <summary>
        /// 填充合同信息属性信息
        /// </summary>
        /// <param name="dr">数据行</param>
        /// <param name="info">合同信息对象</param>
        private static void FillContractInfoContent(DataRow dr, ContractInfo info)
        {
            // 检查数据行参数
            if (dr == null)
            {
                throw new ArgumentNullException("dr", "填充合同信息内容时，合同信息数据行dr不能为空。");
            }
            if (dr["ContractID"] == null || dr["ContractID"].ToString().Trim() == string.Empty)
            {
                throw new ArgumentNullException("ContractID", "填充合同信息内容时，合同信息数据行中的ContractID字段为空值，请检查数据。");
            }

            // 检查合同信息对象
            if (info == null)
            {
                info = new ContractInfo(dr["ContractID"].ToString());
            }

            // 装载合同信息内容
            try
            {
                info.SupplierID = dr["SupplierID"].ToString();
                info.SupplierName = dr["SupplierName"].ToString();
                info.ProjectID = dr["ProjectID"].ToString();
                info.ProjectName = dr["ProjectName"].ToString();
                info.ContractNo = dr["ContractNo"].ToString();
                info.ContractName = dr["ContractName"].ToString();
                if (dr["ContractMoney"]!=null)
                    info.ContractMoney = double.Parse(dr["ContractMoney"].ToString());
                if (dr["ClosedMoney"] != null)
                    info.ClosedMoney = double.Parse(dr["ClosedMoney"].ToString());
                if (dr["StartDate"] != null)
                {
                    info.StartDate = DateTime.Parse(dr["StartDate"].ToString());
                }
                if (dr["EndDate"] != null)
                {
                    info.EndDate = DateTime.Parse(dr["EndDate"].ToString());
                }
                if (dr["CreatorID"] != null)
                {
                    info.CreatorID = dr["CreatorID"].ToString();
                }
                if (dr["CreateTime"] != null)
                {
                    info.CreateTime = DateTime.Parse(dr["CreateTime"].ToString());
                }

                if (dr["Status"] != null)
                {
                    info.Status = (StatusType)Enum.Parse(typeof(StatusType), dr["Status"].ToString());
                }
            }
            catch (Exception ex)
            {
                string msg = string.Format("合同信息\"{0}\"({1})在装载属性信息时出错", info.ContractName, info.ContractID.ToString());
                LogHelper.LogException(ex, msg);

                info = null;
            }
        }

        private static void FillContractInfoParams(DbCommand cmd, ContractInfo info)
        {
            DbParameter ContractID = cmd.CreateParameter();
            ContractID.ParameterName = "ContractID";
            ContractID.Value = info.ContractID.ToString();
            cmd.Parameters.Add(ContractID);

            DbParameter SupplierID = cmd.CreateParameter();
            SupplierID.ParameterName = "SupplierID";
            SupplierID.Value = info.SupplierID.ToString();
            cmd.Parameters.Add(SupplierID);

            DbParameter ProjectID = cmd.CreateParameter();
            ProjectID.ParameterName = "ProjectID";
            ProjectID.Value = info.ProjectID.ToString();
            cmd.Parameters.Add(ProjectID);

            DbParameter ContractNo = cmd.CreateParameter();
            ContractNo.ParameterName = "ContractNo";
            ContractNo.Value = info.ContractNo.ToString();
            cmd.Parameters.Add(ContractNo);

            DbParameter ContractName = cmd.CreateParameter();
            ContractName.ParameterName = "ContractName";
            ContractName.Value = info.ContractName.ToString();
            cmd.Parameters.Add(ContractName);

            DbParameter ContractMoney = cmd.CreateParameter();
            ContractMoney.ParameterName = "ContractMoney";
            ContractMoney.Value = info.ContractMoney;
            cmd.Parameters.Add(ContractMoney);

            DbParameter ClosedMoney = cmd.CreateParameter();
            ClosedMoney.ParameterName = "ClosedMoney";
            ClosedMoney.Value = info.ClosedMoney;
            cmd.Parameters.Add(ClosedMoney);

            DbParameter StartDate = cmd.CreateParameter();
            StartDate.ParameterName = "StartDate";
            StartDate.Value = info.StartDate;
            cmd.Parameters.Add(StartDate);

            DbParameter EndDate = cmd.CreateParameter();
            EndDate.ParameterName = "EndDate";
            EndDate.Value = info.EndDate;
            cmd.Parameters.Add(EndDate);

            DbParameter Creator = cmd.CreateParameter();
            Creator.ParameterName = "Creator";
            Creator.Value = info.CreatorName.ToString();
            cmd.Parameters.Add(Creator);

            DbParameter CreatorID = cmd.CreateParameter();
            CreatorID.ParameterName = "CreatorID";
            CreatorID.Value = info.CreatorID.ToString();
            cmd.Parameters.Add(CreatorID);

            DbParameter CreateTime = cmd.CreateParameter();
            CreateTime.ParameterName = "CreateTime";
            CreateTime.Value = info.CreateTime;
            cmd.Parameters.Add(CreateTime);

            DbParameter Status = cmd.CreateParameter();
            Status.ParameterName = "Status";
            Status.Value = (int)info.Status;
            cmd.Parameters.Add(Status);

            DbParameter userID = cmd.CreateParameter();
            userID.ParameterName = "userID";
            userID.Value = SystemContext.Current.LoginUser.ID;
            cmd.Parameters.Add(userID);
        }
    }
}
